from datetime import date, timedelta
from DataAccess.DBConnFactory import DBConnFactory
from Accounting.Routine.Settle import *
from Accounting.Routine.OpenDayOp import *

from Position.CalcDailyPosition import *
from Mark2Mkt.CalcMktValue import *

def acct_test_case1():
	
	#clear_all_record()
	
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	sql_text = '''insert into asset_value
				(ref_date,entity,av,avpu,curncy,type,official)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''
	
	inception = date(2011, 4, 28)
	d = inception
	v = [(inception.isoformat(),'SC016_F',0,1,'CNY','GAV','T'),
		(inception.isoformat(),'SC016',0,1,'CNY','GAV','T'),
		(inception.isoformat(),'SC016_F',0,1,'CNY','NAV','T'),
		(inception.isoformat(),'SC016',0,1,'CNY','NAV','T')]
	conn.cursor().executemany(sql_text, v)
	conn.commit()
		
	open_day_purchase(d, 'YKY', 10000000, 'CNY', 'SC016_F', 'SC016')
	open_day_purchase(d, 'ZGL', 41000000, 'CNY', 'SC016_F', 'SC016') 
	
	sql_text = '''insert into cash_switch
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	v = [(inception.isoformat(),'SC016',51000000,'CNY')]
	pke_conn.cursor().executemany(sql_text, v)
	pke_conn.commit()				
	
	INSERT_TPL = '''insert into portfolio_mkt_val
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	
	one_day = timedelta(days=1) 	
	amount = 10000000+41000000
	for i in range(1, 60):
		d = d + one_day
		print 'settlement date=', d.isoformat()
		
		if d == date(2011, 5, 16):
			#amount += 1530000*2
			amount -= 1530000*2
			
		if d == date(2011, 6, 15):	
			amount += 20000000
			
		pke_cursor.execute(INSERT_TPL, (d.isoformat(), 'SC016', amount, 'CNY'))
		pke_conn.commit()
			
		daily_clearing(d)
	
		if d == date(2011, 5, 16):
			open_day_purchase(d, 'YKY', 10000000, 'CNY', 'SC016_F', 'SC016')
			#amount += 10000000*1.03
			
		elif d == date(2011, 6, 15):
			cursor = conn.cursor()
			cursor.execute('''select id from share_chg 
						where ref_date=TO_DATE('2011-04-28','yyyy-mm-dd')
						 and parent='SC016_F'
						order by id''')
			r = cursor.fetchall()
			assert len(r) == 2
	
			corr_sub_id1 = r[0][0]
			open_day_redemption(d, 'YKY', 0, 'CNY', 50000, False, corr_sub_id1)
			corr_sub_id2 = r[1][0]
			open_day_redemption(d, 'ZGL', 5000000, 'CNY', 0, True, corr_sub_id2)
			
			#amount -= (5000000+50000*100)
	
		else:
			pass
	
		

def acct_test_case2():
	#clear_all_record()
	
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()	
	
	sql_text = '''insert into asset_value
				(ref_date,entity,av,avpu,curncy,type,official)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''
	
	inception = date(2009, 9, 21)
	v = [(inception.isoformat(),'SC008_A',0,100,'CNY','GAV','T'),
		(inception.isoformat(),'SC008_B',0,100,'CNY','GAV','T'),
		(inception.isoformat(),'SC008_I',0,100,'CNY','GAV','T'),
		(inception.isoformat(),'SC008',0,100,'CNY','GAV','T'),
		(inception.isoformat(),'SC008_A',0,100,'CNY','NAV','T'),
		(inception.isoformat(),'SC008_B',0,100,'CNY','NAV','T'),
		(inception.isoformat(),'SC008_I',0,100,'CNY','NAV','T'),
		(inception.isoformat(),'SC008',0,100,'CNY','NAV','T')]
	conn.cursor().executemany(sql_text, v)
	conn.commit()
		
	open_day_purchase(inception, 'YKY', 1000000, 'CNY', 'SC008_A', 'SC008')
	open_day_purchase(inception, 'ZGL', 2000000, 'CNY', 'SC008_B', 'SC008')
	open_day_purchase(inception, 'XB', 3000000, 'CNY', 'SC008_I', 'SC008')
	
	sql_text = '''insert into cash_switch
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	v = [(inception.isoformat(),'SC008',6000000,'CNY')]
	pke_conn.cursor().executemany(sql_text, v)
	pke_conn.commit()

	INSERT_TPL = '''insert into portfolio_mkt_val
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	
	one_day = timedelta(days=1) 
	d = inception
	amount = 6000000
	for i in range(1, 10):
		d = d + one_day
		print 'settlement date=', d.isoformat()
		
		if d == date(2009, 9, 30):
			amount *= 1.2
			
		pke_cursor.execute(INSERT_TPL, (d.isoformat(), 'SC008', amount, 'CNY'))
		pke_conn.commit()

		daily_clearing(d)
		

def acct_test_case3():
	#clear_all_record()

	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()	
	
	sql_text = '''insert into asset_value
				(ref_date,entity,av,avpu,curncy,type,official)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''
	
	inception = date(2007, 9, 10)
	v = [(inception.isoformat(),'SC028_NONUS',0,100,'USD','GAV','T'),
		(inception.isoformat(),'SC028_NONUS',0,100,'USD','NAV','T'),
		(inception.isoformat(),'SC028_US',0,100,'USD','GAV','T'),
		(inception.isoformat(),'SC028_US',0,100,'USD','NAV','T'),
		(inception.isoformat(),'SC028',0,100,'USD','GAV','T'), 
		(inception.isoformat(),'SC028',0,100,'USD','NAV','T')]
	cursor.executemany(sql_text, v)
	conn.commit()

	#open_day_create_client('Tao Dong', True, None)
	#open_day_create_client('YKY', True, None)
	open_day_purchase(inception, 'Tao Dong', 1000000.00, 'USD', 'SC028_NONUS', 'SC028')
	open_day_purchase(inception, 'YKY', 1000000.00, 'USD', 'SC028_US', 'SC028')
	
	sql_text = '''insert into cash_switch
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	v = [(inception.isoformat(),'SC028',1000000,'USD')]
	pke_conn.cursor().executemany(sql_text, v)
	pke_conn.commit()

	INSERT_TPL = '''insert into portfolio_mkt_val
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	mkt_val = 2000000.00
	
	one_day = timedelta(days=1) 
	d = inception
	for i in range(1, 1000):
		d = d + one_day
		print 'settlement date=', d.isoformat()

		#mkt value change
		if d == date(2007,10,31):
			mkt_val = 95*(2000000.00)/100.00
		if d == date(2007,11,30):
			mkt_val = 110*(2000000.00/100.00+2000000.00/95.00)
		if d == date(2007,12,31):
			mkt_val = 105*(2000000.00/100.00+2000000.00/95.00+100000.00/110)
		if d == date(2008,1,31):
			mkt_val = 120*(2000000.00/100.00+2000000.00/95.00+100000.00/110-10000.00)
		if d == date(2008,2,29):
			pass
		if d == date(2008,12,31):
			mkt_val *= 1.2
		if d == date(2009,1,1):
			return
			
		pke_cursor.execute(INSERT_TPL, (d.isoformat(), 'SC028', mkt_val, 'USD'))
		pke_conn.commit()

		
		#clearing
		daily_clearing(d)

		
		#open day RED/SUB operations
		if d == date(2007,10,31):
			open_day_purchase(d, 'YKY', 2000000.00, 'USD', 'SC028_US', 'SC028')	
		if d == date(2007,11,30):
			open_day_purchase(d, 'Tao Dong', 100000.00, 'USD', 'SC028_NONUS', 'SC028')	
		if d == date(2007,12,31):
			cursor.execute('''select id from share_chg 
						where ref_date=TO_DATE('2007-10-31','yyyy-mm-dd')''')
			corr_sub_id = cursor.fetchone()[0] 
			open_day_redemption(d, 'YKY', None, None, 10000.00, False, corr_sub_id)	
		if d == date(2008,1,31):
			open_day_purchase(d, 'Tao Dong', 500000.00, 'USD', 'SC028_NONUS', 'SC028')	#GAV=110
		if d == date(2008,2,29):
			cursor.execute('''select * from share_chg 
						where (ref_date=TO_DATE('2007-09-10','yyyy-mm-dd')
							or ref_date=TO_DATE('2008-01-31','yyyy-mm-dd'))
							and child='Tao Dong'
							and amount>50000
						order by ref_date''')
			r = cursor.fetchall()
			corr_sub_id1 = r[0][0]
			corr_sub_id2 = r[1][0]
			open_day_redemption(d, 'Tao Dong', None, None, 5000.00, False, corr_sub_id1)
			open_day_redemption(d, 'Tao Dong', 300000, 'USD', None, True, corr_sub_id2)
			
	




	
	